﻿/*
	Cleans the database, removes all generated services, queues and stored procedures.
	Only intended for testing purposes.
*/
CREATE PROCEDURE [dbo].[Cleanup]
AS
BEGIN
	-- Remove services
	DECLARE servicesCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
	FOR
		SELECT name
		FROM sys.services
		WHERE name NOT IN ('InitiatorService', 'RootService') AND LEFT(name, 4) <> 'http'

	OPEN servicesCursor

	DECLARE @name sysname
	DECLARE @sql nvarchar(max)

	FETCH NEXT FROM servicesCursor INTO @name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = N'DROP SERVICE ' + @name
		EXEC sp_executesql @sql	
		FETCH NEXT FROM servicesCursor INTO @name
	END

	CLOSE servicesCursor
	
	-- Remove Queues
	DECLARE queuesCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
	FOR
		SELECT name
		FROM sys.service_queues
		WHERE LEFT(name, 3) IN ('OQ_', 'SQ_')
	
	OPEN queuesCursor

	FETCH NEXT FROM queuesCursor INTO @name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = N'DROP QUEUE ' + @name
		EXEC sp_executesql @sql	
		FETCH NEXT FROM queuesCursor INTO @name
	END

	CLOSE queuesCursor
	
	-- Remove Generated Stored procedures
	DECLARE procsCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
	FOR
		SELECT name
		FROM sys.procedures
		WHERE LEFT(name, 12) = 'OverflowAct_' OR LEFT(name, 16) = 'SubscriptionAct_'

	OPEN procsCursor

	FETCH NEXT FROM procsCursor INTO @name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = N'DROP PROCEDURE ' + @name
		EXEC sp_executesql @sql
		FETCH NEXT FROM procsCursor INTO @name
	END

	CLOSE procsCursor
	
		
	-- Truncate Subscription table
	DELETE FROM Subscription

	-- Delete all rows except for the InitiatorService and RootService from the ServiceMetadata table
	DELETE ServiceMetadata
	WHERE ServiceMetadataId > 2

	UPDATE ServiceMetadata
	SET Source = 'RootService'
	WHERE ServiceMetadataId = 2

	TRUNCATE TABLE MarkedForGC

END